package edu.geo.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import edu.geo.containers.geom.impl.LineSegment;
import edu.geo.containers.geom.impl.Point;
import edu.geo.containers.geomdomain.GeometricDomain;
import edu.geo.dao.IGeometricDomainDao;
import edu.geo.dao.IGeometryDao;
import edu.geo.dao.IGridDao;
import edu.geo.dao.exception.PersistentExceptions;
import edu.geo.db.conn.DBConnection;
import edu.geo.db.conn.DBConnectionProvider;
import edu.geo.exception.GeoInfoException;

/**
 * 
 * @author alejandro.sanchez
 * 
 */
public class GeometricDomainDao implements IGeometricDomainDao {

	/**
	 * 
	 */
	private static final long serialVersionUID = -9164041738978630609L;

	private static final Log LOGGER = LogFactory
			.getLog(GeometricDomainDao.class);

	private static final String INSERT_DOMAIN = "INSERT INTO geometric_domains.geometric_domain("
			+ "gd_id, gd_user) VALUES (?, ?)";

	private static final String INSERT_REF_POINT = "INSERT INTO geometric_domains.geometric_domain_point(gd_id, fe_id) VALUES (?, ?)";

	private static final String INSERT_REF_LINE_SEGMENT = "INSERT INTO geometric_domains.geometric_domain_line_segment(gd_id, fe_id) VALUES (?, ?)";

	private static final String SELECT_GEOM_DOMAIN_ID = "SELECT nextval('geometric_domains.geometric_domain_gd_id_seq')";

	private static final String DELETE_GEOM_DOMAIN = "DELETE FROM geometric_domains.geometric_domain WHERE gd_id = {0}";

	private static final String SELECT_GEOM_DOMAIN = "SELECT gd_user, (SELECT CASE WHEN count(*) = 0 THEN false ELSE true END FROM geometric_domains.fixed_geometric_domains WHERE gd_id = {0})"
			+ "FROM geometric_domains.geometric_domain  WHERE gd_id = {0}";

	private static final String SELECT_GEOM_DOMAINS = "SELECT gd_id FROM geometric_domains.geometric_domain";

	private static final String DELETE_GEOM_DOMAIN_POINT = "DELETE FROM geometric_domains.geometric_domain_point WHERE  fe_id = ? AND gd_id = ?";

	private static final String DELETE_GEOM_DOMAIN_LINE_SEGMENT = "DELETE FROM geometric_domains.geometric_domain_line_segment WHERE fe_id = ? AND gd_id = ?";

	private static final String DELETE_GEOM_DOMAIN_POINTS = "DELETE FROM geometric_domains.geometric_domain_point WHERE gd_id = ?";

	private static final String DELETE_GEOM_DOMAIN_LINE_SEGMENTS = "DELETE FROM geometric_domains.geometric_domain_line_segment WHERE gd_id = ?";

	private static final String DELETE_GEOM_DOMAIN_VALID_DOMAIN = "DELETE FROM geometric_domains.fixed_geometric_domains WHERE fgd_geometric_domain_id = ?";

	private static final String INSERT_VALID_GEOM_DOMAIN = "INSERT INTO geometric_domains.fixed_geometric_domains(fgd_geometric_domain_id, fgd_fixed_geometric_domain_id) VALUES (?, ?)";

	private static final String DELETE_FEATURE = "SELECT deleteFeature( {0}, {1} )";
	
	private DBConnectionProvider connectionProvider;

	private IGridDao gridDao;

	private IGeometryDao geometryDao;

	public GeometricDomainDao(DBConnectionProvider connection) {
		this.connectionProvider = connection;
		gridDao = new GridDao(connectionProvider);
		geometryDao = new GeometryDao(connectionProvider);
	}

	@Override
	public int insertGeometricDomain(GeometricDomain geometricDomain)
			throws GeoInfoException {
		LOGGER.info("createGeometricDomainDao");
		Connection conn = null;
		Statement statement = null;
		ResultSet result = null;
		int gdId = -1;
		try {
			conn = connectionProvider.getConnection();
			conn.setAutoCommit(false);
			// Obtener identificador de la grilla
			statement = conn.createStatement();
			result = statement.executeQuery(SELECT_GEOM_DOMAIN_ID);
			if (result.next()) {
				gdId = result.getInt(1);
				// Insertar registro
				PreparedStatement insertStatement = conn
						.prepareStatement(INSERT_DOMAIN);
				insertStatement.setInt(1, gdId);
				insertStatement.setString(2, geometricDomain.getUser());
				insertStatement.execute();
				// Agrega a la grilla
				conn.commit();
				DBConnection.close(insertStatement);
				// Almacenar grilla
				geometricDomain.getGrid().setGeomDomainId(gdId);
				gridDao.insertGrid(geometricDomain.getGrid());
				// Ingresar puntos
				List<Point> points = geometricDomain.getPoints();
				List<LineSegment> lineSegments = geometricDomain
						.getLineSegments();
				insertGeometries(gdId, points, lineSegments, conn,
						insertStatement);
				geometricDomain.setId(gdId);				
			}
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
		} finally {
			DBConnection.close(result, statement, conn);
		}
		return gdId;
	}

	@Override
	public void deleteGeometricDomainDao(int id) throws GeoInfoException {
		LOGGER.info("deleteGeometricDomainDao");
		Connection conn = null;
		Statement statement = null;
		try {
			conn = connectionProvider.getConnection();
			conn.setAutoCommit(false);
			statement = conn.createStatement();
			// Eliminar el dominio geométrico
			statement.executeUpdate(MessageFormat
					.format(DELETE_GEOM_DOMAIN, id));
			// Esta sentencia no es necesaria porque se agregó DELETE CASCADE
			// gridDao.deleteGrid(geomDomain.getGrid().getId());
			// Eliminar puntos
			geometryDao.deleteUnreferencedLineSegments();
			geometryDao.deleteUnreferencedPoints();
			conn.commit();
		} catch (SQLException e) {
			throw PersistentExceptions.DELETE_GEOM_DOMAIN_ERROR.getExcepcion(
					LOGGER, e, id);
		} finally {
			DBConnection.close(statement, conn);
		}
	}

	@Override
	public GeometricDomain retrieveGeometricDomain(int id)
			throws GeoInfoException {
		LOGGER.info("retrieveGeometricDomainDao");

		Connection conn = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			GeometricDomain geometricDomain = new GeometricDomain();
			conn = connectionProvider.getConnection();
			statement = conn.createStatement();
			resultSet = statement.executeQuery(MessageFormat.format(
					SELECT_GEOM_DOMAIN, id));
			if (resultSet.next()) {
				geometricDomain.setId(id);
				geometricDomain.setUser(resultSet.getString(1));
				geometricDomain.setValid(resultSet.getBoolean(2));
				// Recuperar grilla
				int gid = gridDao.retrieveGridIdByGeomDomainId(id);
				geometricDomain.setGrid(gridDao.retrieveGrid(gid));
				// Recuperar puntos
				geometricDomain.setPoints(geometryDao.retrievePoints(id));
				// Recuperar segmentos de línea
				geometricDomain.setLineSegments(geometryDao
						.retrieveLineSegments(id));
				return geometricDomain;
			} else {
				throw PersistentExceptions.GEOM_DOMAIN_NOT_EXISTS.getExcepcion(
						LOGGER, null, id);
			}
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e, id);
		} finally {
			DBConnection.close(resultSet, statement, conn);
		}
	}

	@Override
	public List<GeometricDomain> retrieveGeometricDomains()
			throws GeoInfoException {
		Connection conn = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			List<GeometricDomain> domains = new ArrayList<GeometricDomain>();
			conn = connectionProvider.getConnection();
			statement = conn.createStatement();
			resultSet = statement.executeQuery(SELECT_GEOM_DOMAINS);
			while (resultSet.next()) {
				domains.add(retrieveGeometricDomain(resultSet.getInt(1)));

			}
			return domains;
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
		} finally {
			DBConnection.close(resultSet, statement, conn );
		}

	}

	@Override
	public void addPoint(int geometricDomainId, Point point)
			throws GeoInfoException {
		LOGGER.info("addPoint");
		if (point == null) {
			throw PersistentExceptions.POINT_IS_NULL.getExcepcion(LOGGER, null);
		}
		Connection conn = null;
		PreparedStatement insertStatement = null;
		try {
			conn = connectionProvider.getConnection();
			conn.setAutoCommit(false);
			int pId = geometryDao.verifyPointExistence(point);
			if (pId == Point.DEFAULT_POINT_ID) {
				pId = geometryDao.insertPoint(point);
			}
			point.setId(pId);
			insertStatement = conn.prepareStatement(INSERT_REF_POINT);
			insertStatement.setInt(1, geometricDomainId);
			insertStatement.setInt(2, pId);
			insertStatement.execute();
			conn.commit();
		} catch (SQLException e) {
			throw PersistentExceptions.ADD_POINT_EXCEPTION.getExcepcion(LOGGER,
					e, point.getAsWKT());
		} finally {
			DBConnection.close(insertStatement, conn);
		}
	}

	@Override
	public void addLineSegment(int geometricDomainId, LineSegment lineSegment)
			throws GeoInfoException {
		LOGGER.info("addLineSegment");
		if (lineSegment == null) {
			throw PersistentExceptions.LINE_SEGMENT_IS_NULL.getExcepcion(
					LOGGER, null);
		}
		Connection conn = null;
		PreparedStatement insertStatement = null;

		try {
			conn = connectionProvider.getConnection();
			conn.setAutoCommit(false);
			int lnSgmId = geometryDao
					.verifyLineSegmentExistenceByGeom(lineSegment);
			if (lnSgmId == Point.DEFAULT_POINT_ID) {
				lnSgmId = geometryDao.insertLineSegment(lineSegment);
			}
			lineSegment.setId(lnSgmId);
			insertStatement = conn.prepareStatement(INSERT_REF_LINE_SEGMENT);
			insertStatement.setInt(1, geometricDomainId);
			insertStatement.setInt(2, lnSgmId);
			insertStatement.execute();
			conn.commit();
		} catch (SQLException e) {
			throw PersistentExceptions.ADD_LINE_SEGMENT_EXCEPTION.getExcepcion(
					LOGGER, e, lineSegment.getAsWKT());
		} finally {
			DBConnection.close(insertStatement, conn);
		}
	}

	@Override
	public void deletePoint(int geomDomainId, Point point)
			throws GeoInfoException {
		LOGGER.info("deletePoint");
		if (point == null) {
			throw PersistentExceptions.POINT_IS_NULL.getExcepcion(LOGGER, null);
		}
		Connection conn = null;
		PreparedStatement deleteStatement = null;
		int pId = point.getId();
		try {
			conn = connectionProvider.getConnection();
			conn.setAutoCommit(false);
			if (pId == Point.DEFAULT_POINT_ID) {
				pId = geometryDao.verifyPointExistence(point);
			}
			deleteStatement = conn.prepareStatement(DELETE_GEOM_DOMAIN_POINT);
			deleteStatement.setInt(1, pId);
			deleteStatement.setInt(2, geomDomainId);
			deleteStatement.execute();
			conn.commit();
		} catch (SQLException e) {
			throw PersistentExceptions.DELETE_POINT_EXCEPTION.getExcepcion(
					LOGGER, e, point.getAsWKT(), geomDomainId);
		} finally {
			DBConnection.close(deleteStatement, conn);
		}
	}

	@Override
	public void deleteLineSegment(int geomDomainId, LineSegment lineSegment)
			throws GeoInfoException {
		LOGGER.info("deletePoint");
		if (lineSegment == null) {
			throw PersistentExceptions.LINE_SEGMENT_IS_NULL.getExcepcion(
					LOGGER, null);
		}
		Connection conn = null;
		PreparedStatement deleteStatement = null;
		int lsId = lineSegment.getId();
		try {
			conn = connectionProvider.getConnection();
			conn.setAutoCommit(false);
			if (lsId == LineSegment.DEFAULT_LINE_SEGMENT_ID) {
				lsId = geometryDao
						.verifyLineSegmentExistenceByGeom(lineSegment);
			}
			deleteStatement = conn
					.prepareStatement(DELETE_GEOM_DOMAIN_LINE_SEGMENT);
			deleteStatement.setInt(1, lsId);
			deleteStatement.setInt(2, geomDomainId);
			deleteStatement.execute();
			conn.commit();
		} catch (SQLException e) {
			throw PersistentExceptions.DELETE_LINE_SEGMENT_EXCEPTION
					.getExcepcion(LOGGER, e, lineSegment.getAsWKT(),
							geomDomainId);
		} finally {
			DBConnection.close(deleteStatement, conn);
		}
	}

	@Override
	public boolean insertValidGeometricDomain(int originalGeomDomainId, GeometricDomain geometricDomain)
			throws GeoInfoException {
		LOGGER.info("Insert valid geometric domain");
		int id = insertGeometricDomain(geometricDomain);
		Connection conn = null;
		PreparedStatement insertStatement = null;
		try {
			conn = connectionProvider.getConnection();
			conn.setAutoCommit(false);
			insertStatement = conn
					.prepareStatement(INSERT_VALID_GEOM_DOMAIN);
			insertStatement.setInt(1, originalGeomDomainId);
			insertStatement.setInt(2, id);
			insertStatement.execute();
			conn.commit();
		} catch (SQLException e) {
			throw PersistentExceptions.INSERT_VALID_GEOM_DOMAIN_EXCEPTION
					.getExcepcion(LOGGER, e, originalGeomDomainId);
		} finally {
			DBConnection.close(insertStatement, conn);
		}
		return false;
	}

	@Override
	public IGeometryDao getGeometryDao() {
		return geometryDao;
	}

	@Override
	public boolean modifyGeometriesDomain(int geomDomainId, List<Point> points,
			List<LineSegment> lineSegments) throws GeoInfoException {
		LOGGER.info("modifyGeometriesDomain");
		Connection conn = null;
		PreparedStatement deleteStatement = null;
		try {
			conn = connectionProvider.getConnection();
			conn.setAutoCommit(false);
			if (points != null) {
				deleteStatement = conn
						.prepareStatement(DELETE_GEOM_DOMAIN_POINTS);
				deleteStatement.setInt(1, geomDomainId);
				deleteStatement.execute();
				conn.commit();
				DBConnection.close(deleteStatement);
			}
			if (lineSegments != null) {
				deleteStatement = conn
						.prepareStatement(DELETE_GEOM_DOMAIN_LINE_SEGMENTS);
				deleteStatement.setInt(1, geomDomainId);
				deleteStatement.execute();
				conn.commit();
				DBConnection.close(deleteStatement);
			}
			deleteStatement = conn
					.prepareStatement(DELETE_GEOM_DOMAIN_VALID_DOMAIN);
			deleteStatement.setInt(1, geomDomainId);
			deleteStatement.execute();
			conn.commit();
			DBConnection.close(deleteStatement);
			insertGeometries(geomDomainId, points, lineSegments, conn,
					deleteStatement);
		} catch (SQLException e) {
			throw PersistentExceptions.MODIFY_GEOM_DOMAIN_GEOMETRIES_EXCEPTION
					.getExcepcion(LOGGER, e, geomDomainId);
		} finally {
			DBConnection.close(deleteStatement, conn);
		}
		return false;
	}

	private void insertGeometries(int gdId, List<Point> points,
			List<LineSegment> lineSegments, Connection conn,
			PreparedStatement insertStatement) throws SQLException,
			GeoInfoException {
		// Ingresar puntos
		if (points != null) {
			for (Point point : points) {
				int pId = geometryDao.verifyPointExistence(point);
				if (pId == Point.DEFAULT_POINT_ID) {
					pId = geometryDao.insertPoint(point);
				}
				point.setId(pId);
			}
		}
		conn.commit();
		// insertar puntos
		if (points != null) {
			insertStatement = conn.prepareStatement(INSERT_REF_POINT);
			for (Point point : points) {
				LOGGER.info("Id point: " + point.getId());
				insertStatement.setInt(1, gdId);
				insertStatement.setInt(2, point.getId());
				insertStatement.execute();
			}
		}
		conn.commit();
		insertStatement.close();
		// Ingresar líneas
		if (lineSegments != null) {
			for (LineSegment lineSegment : lineSegments) {
				int lsId = geometryDao
						.verifyLineSegmentExistenceByGeom(lineSegment);
				if (lsId == LineSegment.DEFAULT_LINE_SEGMENT_ID) {
					lsId = geometryDao.insertLineSegment(lineSegment);
				}
				lineSegment.setId(lsId);
			}
		}
		conn.commit();
		insertStatement.close();
		// Insertar líneas
		if (lineSegments != null) {
			insertStatement = conn.prepareStatement(INSERT_REF_LINE_SEGMENT);
			for (LineSegment lineSegment : lineSegments) {
				LOGGER.info("Id LS: " + lineSegment.getId());
				insertStatement.setInt(1, gdId);
				insertStatement.setInt(2, lineSegment.getId());
				insertStatement.execute();
			}
		}
		conn.commit();
		insertStatement.close();
	}

	@Override
	public IGridDao getGridDao() {
		return gridDao;
	}

	@Override
	public void deleteFeature(int geomDomainId, int featureId) throws GeoInfoException {
			Connection conn = null;
			Statement statement = null;
			try {
				conn = connectionProvider.getConnection();
				conn.setAutoCommit(false);
				statement = conn.createStatement();
				String deleteQry = MessageFormat.format(DELETE_FEATURE, Integer.toString(geomDomainId), Integer.toString(featureId));
				statement.executeQuery(deleteQry);	
				conn.commit();
			} catch (SQLException e) {
				throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
			}
			finally
			{
				DBConnection.close(statement, conn);
			}			
		
	}
}
